package com.geekchique.runningbuddy.provider;

import java.util.Calendar;

import com.geekchique.runningbuddy.util.DBHelperTools;

public class RunDBSQL {
	public static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "runningBuddyDB2";

	public static final String RUNNING_ENTRIES_TABLE = "jog";

	public static final String TIME_TABLE = "time";

	public static final String RUN_ID = "_id";
	public static final String RUN_NAME = "name";
	public static final String RUN_ROUTE_ID = "_idRoute";
	public static final String RUN_TOTAL_TIME = "totalTime";
	public static final String RUN_CREATED_AT = "createdAt";
	public static final String RUN_MODIFIED_AT = "modifiedAt";

	public static final String RUNSEGMENT_ID = "_id";
	public static final String RUNSEGMENT_RUN_ID = "_idRun";
	public static final String RUNSEGMENT_CHECKPOINT_POINT_ID = "_idCheckPoint";
	public static final String RUNSEGMENT_TIME = "time";
	public static final String RUNSEGMENT_LAP = "lap";
	public static final String RUNSEGMENT_CHECKPOINT_NO = "checkPointNo";
	public static final String RUNSEGMENT_CREATED_AT = "createdAt";
	public static final String RUNSEGMENT_MODIFIED_AT = "modifiedAt";

	public static final String ROUTESEGMENT_ID = "_id";
	public static final String ROUTESEGMENT_CHECKPOINT_ID = "_idCheckPoint";
	public static final String ROUTESEGMENT_ROUTEPOINT_ID = "_idRoute";
	public static final String ROUTESEGMENT_CHECKPOINT_NO = "checkPointNo";
	public static final String ROUTESEGMENT_DISTANCE = "distance";

	// All Results come back in the following results table
	public static final int RUNRESULTS_RUN_ID_COL = 0;
	public static final int RUNRESULTS_NAME_COL = 1;
	public static final int RUNRESULTS_ROUTE_ID_COL = 2;
	public static final int RUNRESULTS_POINT_NO_COL = 3;
	public static final int RUNRESULTS_LONGITUDE_COL = 4;
	public static final int RUNRESULTS_LATITUDE_COL = 5;
	public static final int RUNRESULTS_TIME_COL = 6;
	public static final int RUNRESULTS_LAP_COL = 7;

	/*
	 * Database Layout
	 * 
	 * jog - Stores each jog entry route - Each jog has a route associated with
	 * it. routePoint - Each route is made from stored points from the journey
	 * time - Times are are associated with points on route during a jog
	 * 
	 */

	// public static final String CREATE_JOG_TABLE = "CREATE TABLE jog (_id
	// INTEGER PRIMARY KEY, name TEXT);";
	//
	// public static final String CREATE_ROUTE_TABLE = "CREATE TABLE route (_id
	// INTEGER PRIMARY KEY, _jogId INTEGER);";
	//
	// public static final String CREATE_ROUTE_POINT_TABLE = "CREATE TABLE
	// routePoint (_id INTEGER PRIMARY KEY, pointNo NUMERIC, _routeId INTEGER,
	// latitude NUMERIC, longitude NUMERIC);";
	//
	// public static final String CREATE_TIME_TABLE = "CREATE TABLE time (_id
	// INTEGER PRIMARY KEY, jogId NUMERIC, routePointId NUMERIC, routeId
	// NUMERIC, lap BOOLEAN, time DATETIME);";
	// END-DB STRUCTURE

	public static String[] COLUMNS_IN_RUN_ENTRY = new String[] { RUN_ID,
			RUN_NAME, RUN_TOTAL_TIME, RUN_MODIFIED_AT, RUN_CREATED_AT, };

	public static String[] COLUMNS_IN_TIME_ENTRY = new String[] { RUNSEGMENT_ID,
			RUNSEGMENT_RUN_ID, RUNSEGMENT_CHECKPOINT_POINT_ID, RUNSEGMENT_CHECKPOINT_NO,
			RUNSEGMENT_TIME, RUNSEGMENT_LAP, RUNSEGMENT_CREATED_AT, RUNSEGMENT_MODIFIED_AT };



	public static String[] COLUMNS_IN_RUNSEGMENT_ENTRY = new String[] {
			ROUTESEGMENT_ID, ROUTESEGMENT_CHECKPOINT_ID, ROUTESEGMENT_ROUTEPOINT_ID,
			ROUTESEGMENT_CHECKPOINT_NO, ROUTESEGMENT_DISTANCE };

	public static final String GET_ALL_JOG_ENTRIES = "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
			+ "FROM 	jog "
			+ "INNER JOIN "
			+ "( "
			+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
			+ "	FROM 	time startTimes "
			+ "	WHERE 	startTimes.time = ( "
			+ "			SELECT	jog.time "
			+ "			FROM 	time jog "
			+ "			WHERE 	jog.jogId = startTimes.jogId "
			+ "			ORDER BY jog.routePointId ASC "
			+ "			LIMIT 1 "
			+ "			) "
			+ ") jogStartTimes "
			+ "ON jog._id = jogStartTimes._jogId "
			+ "INNER JOIN "
			+ "( "
			+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
			+ "	FROM 	time stopTimes "
			+ "	WHERE 	stopTimes.time = "
			+ "		( "
			+ "			SELECT	jog.time "
			+ "			FROM 	time jog "
			+ "			WHERE 	jog.jogId = stopTimes.jogId "
			+ "			ORDER BY jog.routePointId DESC "
			+ "			LIMIT 1 "
			+ "		) "
			+ ") jogStopTimes " + "ON jog._id = jogStopTimes._jogId";

	public static final String FETCH_ALL_TODAYS_JOG_ENTRY_START_STOP_TIMES = "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
			+ "FROM 	jog "
			+ "INNER JOIN "
			+ "( "
			+ "	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
			+ "	FROM 	time startTimes "
			+ "	WHERE 	startTimes.time = ( "
			+ "			SELECT	jog.time "
			+ "			FROM 	time jog "
			+ "			WHERE 	jog.jogId = startTimes.jogId "
			+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
			+ "			ORDER BY jog.routePointId ASC "
			+ "			LIMIT 1 "
			+ "			) "
			+ ") jogStartTimes "
			+ "ON jog._id = jogStartTimes._jogId "
			+ "INNER JOIN "
			+ "( "
			+ "	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
			+ "	FROM 	time stopTimes "
			+ "	WHERE 	stopTimes.time = "
			+ "		( "
			+ "			SELECT	jog.time "
			+ "			FROM 	time jog "
			+ "			WHERE 	jog.jogId = stopTimes.jogId "
			+ "			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
			+ "			ORDER BY jog.routePointId DESC "
			+ "			LIMIT 1 "
			+ "		) "
			+ ") jogStopTimes " + "ON jog._id = jogStopTimes._jogId";

	public static String FETCH_ALL_TODAYS_JOG_ENTRIES = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
			+ "FROM route "
			+ "INNER JOIN ("
			+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
			+ "			FROM routePoint, route "
			+ "			WHERE route._id = routePoint._routeId "
			+ "		) routeandpoints "
			+ "ON route._id = routeandpoints._routeId "
			+ "INNER JOIN ("
			+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
			+ "			FROM time, route "
			+ "			WHERE route._id = time.routeId "
			+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'now') "
			+ "		) timesroutesandpoints "
			+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
			+ "INNER JOIN ( "
			+ "			SELECT jog._id AS _jogId, jog.name AS name "
			+ "			FROM jog "
			+ "		) jogdetails " + "ON route._jogId = jogdetails._jogId";

	public static String selectAJogEntry(String jogId) {
		return "  SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route"
				+ "			WHERE route._id = routePoint._routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND route._jogId = "
				+ jogId
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ("
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog"
				+ "			WHERE jog._id = "
				+ jogId
				+ "		) jogdetails " + "ON route._jogId = jogdetails._jogId";
	}

	public static String FETCH_STUBBEDOUT_JOG_ENTRIES = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap"
			+ "FROM route "
			+ "INNER JOIN ("
			+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude"
			+ "			FROM routePoint, route"
			+ "			WHERE route._id = routePoint._routeId "
			+ "		) routeandpoints "
			+ "ON route._id = routeandpoints._routeId "
			+ "INNER JOIN ("
			+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId"
			+ "			FROM time, route"
			+ "			WHERE route._id = time.routeId "
			+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'2008-03-10T10:00:00:00.000') "
			+ "		) timesroutesandpoints "
			+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
			+ "INNER JOIN ("
			+ "			SELECT jog._id AS _jogId, jog.name AS name"
			+ "			FROM jog"
			+ "		) jogdetails "
			+ "ON route._jogId = jogdetails._jogId";

	public static String getJogId(String jogName) {
		return "SELECT _id " + "FROM jog " + "WHERE name = " + jogName
				+ "LIMIT 1";
	}

	public static String insertJogName(String jogName) {
		return "INSERT INTO jog (name) VALUES ( \'" + jogName + "\')";
	}

	public static String selectJustEnteredJogId() {
		return "SELECT _id " + "FROM jog " + "ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String addATimeEntry(String jogId, String routeId,
			String routePointId, String time, String lap) {
		return "INSERT INTO time (jogId, routePointId, routeId, time, lap) "
				+ "VALUES (\'" + jogId + "\',\'" + routePointId + "\',\'"
				+ routeId + "\',\'" + time + "\',\'" + lap + "\');";
	}

	public static final String WHERE_ENTRIES_WERE_TODAY = "strftime( \"%Y-%m-%d\","
			+ RUNSEGMENT_TIME + ") = strftime( \"%Y-%m-%d\",'now');";

	public static final String WHERE_ENTRIES_WERE_THIS_MONTH = "strftime( \"%Y-%m\","
			+ RUNSEGMENT_TIME + ") = strftime( \"%Y-%m\",'now');";

	public static String whereEntriesWereThisMonth(Calendar calendar) {
		String query = "strftime( \"%Y-%m\"," + RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m\",'"
				+ DBHelperTools.isoFormat(calendar.getTimeInMillis())
				+ "');";

		return query;
	}

	// Entries must be entered into DB in YYYY-MM-DDTHH:MM:SS.SSS format
	public static String getWhereCondition(Integer day, Integer month,
			Integer year) {
		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		return "strftime( \"%Y-%m-%d\"," + RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m-%d\",\"" + year.toString() + "-"
				+ monthString + "-" + dayString + "T12:00:00.000\");";
	}

	public static String GET_ENTRIES(Integer day, Integer month, Integer year) {

		String dayString;
		String monthString;

		if (day < 10) {
			dayString = "0" + day.toString();
		} else {
			dayString = day.toString();
		}

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m-%d\","
				+ RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m-%d\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-"
				+ dayString
				+ "T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}

	public static String GET_MONTH_ENTRIES(Integer month, Integer year) {

		String monthString;

		if (month < 10) {
			monthString = "0" + month.toString();
		} else {
			monthString = month.toString();
		}

		String query = "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
				+ "FROM route "
				+ "INNER JOIN ("
				+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
				+ "			FROM routePoint, route "
				+ "			WHERE route._id = routePoint._routeId "
				+ "		) routeandpoints "
				+ "ON route._id = routeandpoints._routeId "
				+ "INNER JOIN ("
				+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
				+ "			FROM time, route "
				+ "			WHERE route._id = time.routeId "
				+ "			AND	strftime( \"%Y-%m\","
				+ RUNSEGMENT_TIME
				+ ") = strftime( \"%Y-%m\",\'"
				+ year.toString()
				+ "-"
				+ monthString
				+ "-01T12:00:00.000\') "
				+ "		) timesroutesandpoints "
				+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
				+ "INNER JOIN ( "
				+ "			SELECT jog._id AS _jogId, jog.name AS name "
				+ "			FROM jog "
				+ "		) jogdetails "
				+ "ON route._jogId = jogdetails._jogId";

		return query;

	}

}
